﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

namespace WebBanHang
{
    public class DataProvider
    {

        public static SqlConnection ConnectionData()
        {
           
            //  string str = "Data Source=.\\SQLEXPRESS;Initial Catalog=WebDeal;Persist Security Info=True;User ID=sa;Password=123";
            string str = "Data Source=.;Initial Catalog=WebDeal;Integrated Security=SSPI;User ID=sa;PassWord=123;";
            SqlConnection cn;
            cn = new SqlConnection(str);
            cn.Open();

            return cn;
        }
        public static DataTable LoadTable(string sql)
        {
            SqlConnection cn = DataProvider.ConnectionData();
            SqlDataAdapter da = new SqlDataAdapter(sql, cn);
            DataTable dt = new DataTable();           

                da.Fill(dt);
            return dt;
        }
        public static DataTable LoadTablePara(string sql, string[] name, object[] value, int Nparameter)
        {
            SqlConnection cn = DataProvider.ConnectionData();
            SqlCommand cmd = new SqlCommand(sql, cn);
            cmd.CommandType = CommandType.StoredProcedure;
            for (int i = 0; i < Nparameter; i++)
            {
                cmd.Parameters.AddWithValue(name[i], value[i]);

            }
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
        public static void ExecuteNonQuery(SqlCommand cmd, SqlParameter[] pr)
        {
            cmd.Connection = DataProvider.ConnectionData();
            for (int i = 0; i < pr.Length; i++)
            {
                cmd.Parameters.Add(pr[i]);
            }
            cmd.ExecuteNonQuery();
            cmd.Connection.Close();
        }

        public static DataTable LoadTable(SqlCommand cmd, SqlParameter[] pr)
        {
            cmd.Connection = DataProvider.ConnectionData();
            for (int i = 0; i < pr.Length; i++)
            {
                cmd.Parameters.Add(pr[i]);
            }
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            
            DataTable dt = new DataTable();
            try
            {
                da.Fill(dt);
            }
            catch (System.Exception ex)
            {

            }
            return dt;
        }

        public static void ExecuteNonQuery(string sql)
        {
            SqlConnection cn = DataProvider.ConnectionData();
            SqlCommand cmd = new SqlCommand(sql, cn);
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch 
            {
                //MessageBox.Show(ex.Message.ToString());
            }
            cn.Close();
        }
        public static object ExecuteScalar(string sql)
        {
            object value = null;
            SqlConnection cn = DataProvider.ConnectionData();
            SqlCommand cmd = new SqlCommand(sql, cn);
            try
            {
                value = cmd.ExecuteScalar();
            }
            catch 
            {
                //MessageBox.Show(ex.Message.ToString());
            }
            cn.Close();
            return value;
        }
        public static object ExecuteScalar(string sql, SqlParameter[] pr)
        {
            SqlConnection cn = DataProvider.ConnectionData();
            object value = null;
            SqlCommand cmd = new SqlCommand(sql, cn);
            cmd.CommandType = CommandType.StoredProcedure;
            for (int i = 0; i < pr.Length; i++)
            {
                cmd.Parameters.Add(pr[i]);

            }
            try
            {
                value = cmd.ExecuteScalar();
            }
            catch 
            {
                //MessageBox.Show(ex.Message.ToString());
            }
            cn.Close();
            return value;
        }
    }
}